package util.core.plugins.mycraft.cc;

import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.bukkit.craftbukkit.libs.jline.internal.Log;
import entity.core.plugins.mycraft.cc.SqlComStru;
import entity.core.plugins.mycraft.cc.SqlLink;
import entity.core.plugins.mycraft.cc.config;
import entity.core.plugins.mycraft.cc.ColStruct;
import implate.core.plugins.mycraft.cc.GenSQLCommandImplate;
import implate.core.plugins.mycraft.cc.Mycraft;

public class GenMySQLCommand extends Mycraft implements GenSQLCommandImplate {
	private SqlComStru ss;
	private PreparedStatement sqlCommand;
	private SqlLink ps;
	private List<ColStruct> gCols = null;
	private String tbName = "";

	GenMySQLCommand() {
		this.ps = null;
	}

	GenMySQLCommand(SqlLink s, config cf) {
		this.ps = s;
		this.G_cf = cf;
		this.ss = new SqlComStru();
		this.sqlCommand = null;
	}

	private void finish() {
		// TODO Auto-generated method stub
		if (this.G_cf.getDebug() == 1) Log.info(this.sqlCommand.toString());
	}

	private List<ColStruct> GotTBDetail(String tb) throws SQLException {
		List<ColStruct> list = null;
		list = new ArrayList<ColStruct>();
		DatabaseMetaData dbmd = this.ps.getLink().getMetaData();
		ResultSet rs = dbmd.getColumns(null, "%", tb, "%");
		while (rs.next()) {
			ColStruct cols = new ColStruct();
			cols.setColName(rs.getString("COLUMN_NAME"));
			cols.setColType(rs.getString("TYPE_NAME"));
			cols.setColSize(rs.getInt("COLUMN_SIZE"));
			cols.setColDigits(rs.getInt("DECIMAL_DIGITS"));
			cols.setNullAble(rs.getInt("NULLABLE"));
			cols.setAutoIncrement(rs.getBoolean("IS_AUTOINCREMENT"));
			list.add(cols);
		}
		this.gCols = list;
		this.tbName = tb;
		return list;
	}

	private ColStruct GotTBDetail(String tb, String colName) throws SQLException {
		ColStruct col1 = null;
		if (this.tbName.isEmpty() || this.gCols == null) this.GotTBDetail(tb);
		if (this.tbName.equals(tb)) {
			for (ColStruct s : this.gCols) {
				if (s.getColName().toLowerCase().equals(colName)) {
					col1 = s;
					break;
				}
			}
		}
		return col1;
	}

	/**
	 * 用以生成直接执行的数据库语句, 如 DROP, CREATE 语句
	 * 
	 * @param sql
	 *            要执行的语句
	 */
	public void MakeExec(String sql) {
		try {
			this.sqlCommand = this.ps.getLink().prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * Insert INTO {0}({1}) VALUES({2}) <br/>
	 * {0}, tb, Must, Default will be error return <br/>
	 * {1}, filed, Must, Default All From table fields by *<br/>
	 * {2}, Values, Must, Default will be error return <br/>
	 * Example: <br/>
	 * [ "user", "*", "1,2,3,4" ] => INSER INTO `user`(DESC `user`) VALUES(1,2,3,4)
	 * <br/>
	 * [ "user", "i,m,n,e", "1,2,3,4" ] => INSER INTO `user`(i,m,n,e)
	 * VALUES(1,2,3,4) <br/>
	 * 
	 * @param sql
	 * @throws SQLException 
	 */
	@Override
	public void MakeInsert(List<String> sql) throws SQLException {
		// TODO Auto-generated method stub
		if (sql.size() >= 3) {
			if (sql.get(1).toString().equals("*") || sql.get(1).isEmpty()) {
				List<ColStruct> cols = this.GotTBDetail(sql.get(1).toString());
				List<String> nameDict = new ArrayList<String>();
				for (ColStruct col : cols) // got columns
					if (col.isAutoIncrement()) continue;
					else
						nameDict.add(col.getColName());
				List<String> valDict = Arrays.asList(sql.get(2).toString().split(","));
				if (nameDict.size() != valDict.size()) // cmp key : value
				    Log.error("GenSqlCommand Fail: Make Insert Value is reqiure");
				else {
					String[] t = nameDict.toArray(new String[nameDict.size()]);
					String t1 = "(";
					for (String t2 : t)
						t1 = t1 + "`" + t2.toString() + "`,";
					t1 = t1.substring(0, (t1.length() - 1)) + ")";
					sql.set(1, t1);
				}
			} else {
				sql.set(1, "(" + sql.get(1) + ")");
			}
			this.ss.setType("INSERT INTO");
			this.ss.setField(sql.get(0));
			this.ss.setTb(sql.get(1));
			this.ss.setWhere(sql.get(2));
			try {
				String sss = this.ss.GetComm(); // format sql command
				this.sqlCommand = this.ps.getLink().prepareStatement(sss);
				String[] t = sql.get(2).split(",");
				int i = 0;
				for (String t1 : t) {
					i++;
					this.sqlCommand.setString(i, t1.toString());
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else
			Log.error("GenSqlCommand Fail: Make Insert parameter is reqiure");
	}

	/**
	 * UPDATE {0} SET {1,2} WHERE {3} <br/>
	 * {0}, table, Must, Default will be error return <br/>
	 * {1}, field, Must, Default will be error return <br/>
	 * {2}, value, Must, Default will be error return <br/>
	 * {3}, condition, Must, Default will be error return <br/>
	 * Example: <br/>
	 * [ "test", "pass {=} delovt", "id=:i:","i{=}1"] => UPDATE test SET
	 * pass='delovt' WHERE id=1
	 * @throws SQLException 
	 */
	@Override
	public void MakeUpdate(List<String> sql) throws SQLException {
		// TODO Auto-generated method stub
		if (sql.size() == 4) {
			String[] fileds = sql.get(1).split(",");
			String[] condis = sql.get(2).split(",");
			String[] values = sql.get(3).split(",");
			List<String> fp = new ArrayList<String>();
			List<String> fpv = new ArrayList<String>();
			List<String> cp = new ArrayList<String>();
			for (String f : fileds) { // check columns
				String[] fs = f.split("\\{.*\\}");
				if (this.GotTBDetail(sql.get(0), fs[0].trim()) != null) {
					fp.add(fs[0].trim() + "= ?");
					fpv.add(fs[1].trim());
				} else
					Log.error("tb: ", sql.get(0), ", columns: ", fs[0].trim(), " fail, fileds not found");
			}
			sql.set(1, "SET " + this.ListToString(fp, ","));
			for (String f : condis) { // check conditions
				String[] fs = f.split("\\{.*\\}");
				if (this.GotTBDetail(sql.get(0), fs[0].trim()) == null) {
					Log.error("tb: ", sql.get(0), ", condition: ", fs[0].trim(), " fail, fileds not found");
				} else {
					cp.add(fs[0].trim() + "=?");
					String t = fs[1].trim();
					t = t.substring(1, t.length() - 1);
					for (String s : values) {
						String[] fb = s.split("\\{.*\\}");
						if (fb[0].trim().equals(t)) fpv.add(fb[1].trim());
					}
				}
			}
			if (cp.size() == values.length) { // check condition and value => gene sql
				this.ss.setType("UPDATE");
				this.ss.setField(sql.get(0));
				this.ss.setTb(sql.get(1));
				this.ss.setWhere(sql.get(2));
				try {
					String sss = this.ss.GetComm(); // format sql command
					this.sqlCommand = this.ps.getLink().prepareStatement(sss);
					int i = 0;
					for (String t1 : fpv) {
						i++;
						this.sqlCommand.setString(i, t1.toString());
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			} else
				Log.error("GenSqlCommand Fail: Make Upadte fileds and Condition not equal");
		} else
			Log.error("GenSqlCommand Fail: Make Update parameter is require");
	}

	/**
	 * DELETE FROM {0} WHERE {1,2} {0}, table, Must, Default will be error return
	 * <br/>
	 * {1}, condition, Must, Default will be error return <br/>
	 * {2}, value, Must, Default will be error return <br/>
	 * Example: <br/>
	 * [ "test", "id{=}:i:","i{=}2" ] => DELETE FROM test WHERE id=2
	 * @throws SQLException 
	 */
	@Override
	public void MakeDelete(List<String> sql) throws SQLException {
		// TODO Auto-generated method stub
		if (sql.size() == 3) {
			this.ss.setType("DELETE FROM");
			this.ss.setField(sql.get(0));
			String[] filds = sql.get(1).trim().split(",");
			String[] values = sql.get(2).trim().split(",");
			List<String> fs = new ArrayList<String>();
			List<String> vs = new ArrayList<String>();
			List<String> k = new ArrayList<String>();
			for (String f : filds) {
				String[] t = f.split("\\{.*\\}");
				if (this.GotTBDetail(sql.get(0).trim(), t[0].trim()) != null) fs.add(f);
				k.add(t[1].trim());
			}
			for (String f : values) {
				String[] t = f.split("\\{=\\}");
				String t1 = t[0].trim();
				t1 = ":" + t1 + ":";
				if (k.indexOf(t1) != -1) {
					vs.add(t[1].trim());
				}
			}
			if (fs.size() == vs.size()) {
				try {
					String sss = this.ListToString(fs, ",");
					sql.set(1, sss);
					this.ss.setTb("WHERE");
					this.ss.setWhere(sql.get(1));
					sss = this.ss.GetComm();
					this.sqlCommand = this.ps.getLink().prepareStatement(sss);
					int i = 0;
					for (String s : vs) {
						i++;
						this.sqlCommand.setString(i, s);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			} else
				Log.error("GenSqlCommand Fail: Make Delete columns Fail ", this.ListToString(k));
		} else
			Log.error("GenSqlCommand Fail: Make Delete parameter is require");
	}

	/**
	 * Select {0} From {1} WHERE {2,4} Limit {3} <br/>
	 * {0}, field, Must, Default `*` <br/>
	 * {1}, table, Must, Default will be error return <br/>
	 * {2}, condition, Non Must, Default Null <br/>
	 * {3}, Limit, Non Must, Default `0,500` <br/>
	 * {4}, Value, Require if condition, Default Null <br/>
	 * Example: <br/>
	 * [ "*", "user" ] => SELECT `*` FROM `db`.`user` LIMIT 0,500 <br/>
	 * [ "*", "user", "a{=}:a:,b {like} :b:","a{=}1,b{=}1%","" ] => SELECT `*` FROM
	 * `user` WHERE `a=1` AND b like `1%` LIMIT 0,500<br/>
	 * [ "*", "user", "b{=}:b:", "b{=}1", "0,10" ] => SELECT `*` FROM `user` WHERE
	 * `b=1` LIMIT 0,10 <br/>
	 * 
	 * @param sql
	 * @throws SQLException
	 */
	@Override
	public void MakeSearch(List<String> sql) throws SQLException {
		// TODO Auto-generated method stub
		if (sql.size() >= 2) {
			if (!sql.get(1).substring(0, 4).toString().equals("FROM"))
			    sql.set(1, "FROM ".toString() + sql.get(1).toString());
		} else
			Log.error("GenSqlCommand Fail: Make Search parameter is reqiure", this.ListToString(sql));
		this.ss.setType("SELECT");
		this.ss.setField(sql.get(0));
		this.ss.setTb(sql.get(1));
		if (sql.size() >= 3) this.ss.setWhere(sql.get(2));
		if (sql.size() >= 4) this.ss.setLimit(sql.get(4));
		String sss = this.ss.GetComm();
		this.sqlCommand = this.ps.getLink().prepareStatement(sss);
		if (this.ss.flag) {
			String[] ks = sql.get(2).split(",");
			String[] vs = sql.get(3).split(",");
			if (this.G_cf.getDebug() == 1) {
				Log.info("[debug] MycraftPluginCore -> MakeSearch: ", sql.get(2).toString(), " => ",
				        sql.get(3).toString());
			}
			int i = 0;
			for (String k : ks) {
				i++;
				String[] tks = k.replace(" ", "").split("\\{.*\\}");
				for (String v : vs) {
					String[] tvs = v.split("\\{=\\}");
					if (this.G_cf.getDebug() == 1) {
						Log.info("[debug] MycraftPluginCore -> MakeSearch: ", k.toString(), " => ", v.toString());
					}
					if (tks[1].substring(1, (tks[1].length() - 1)).equals(tvs[0])) {
						this.sqlCommand.setString(i, tvs[1].toString());
						break;
					}
				}
			}
		}
	}

	public PreparedStatement getSqlCommand() {
		if (sqlCommand == null) Log.error("Gen SQL Fail");
		this.ss = new SqlComStru();
		this.finish();
		return this.sqlCommand;
	}

	public SqlComStru getSs() {
		return ss;
	}

	public void setSs(SqlComStru ss) {
		this.ss = ss;
	}

	private String ListToString(List<String> sql) {
		String[] ss = sql.toArray(new String[(sql.size())]);
		String s = "";
		for (String t : ss) {
			s = s + " " + t;
		}
		return s;
	}

	private String ListToString(List<String> sql, String q) {
		String[] ss = sql.toArray(new String[(sql.size())]);
		String s = "";
		for (String t : ss) {
			s = s + t + q;
		}
		return s.substring(0, (s.length() - 1));
	}

	public SqlLink getPs() {
		return ps;
	}

	public void setPs(SqlLink ps) {
		this.ps = ps;
	}

}
